package com.gzhryc.jdbc.dialect.h2;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.gzhryc.jdbc.dialect.AbsDbHelper;
import com.gzhryc.jdbc.models.Db;
import com.gzhryc.jdbc.models.DbColumn;
import com.gzhryc.jdbc.models.DbTable;
import com.gzhryc.jdbc.models.IndexInfo;

public class H2DbHelper extends AbsDbHelper{

	public H2DbHelper(String connectionKey) {
		super(connectionKey);
	}

	public List<Db> findAllDb() throws SQLException {
		Connection conn = null;
		ResultSet rs = null;
		try{
			List<Db> result =  new ArrayList<Db>();
			conn = getManageConn();
			Statement stmt = conn.createStatement();
			rs = stmt.executeQuery("select * from INFORMATION_SCHEMA.SCHEMATA");
			while(rs.next()){
				Db db = new Db();
				db.setName(rs.getString("SCHEMA_NAME"));
				result.add(db);
			}
			return result;
		}finally{
			if(rs != null){
				rs.close();
			}
			if(conn != null){
				conn.close();
			}
		}
	}

	public List<DbTable> findAllDbTable(String database) throws SQLException {
		Connection conn = null;
		ResultSet rs = null;
		try{
			List<DbTable> result = new ArrayList<DbTable>();
			conn = getManageConn();
			PreparedStatement pstmt = conn.prepareStatement("select * from INFORMATION_SCHEMA.TABLES");
			//pstmt.setString(1, database);
			rs = pstmt.executeQuery();
			while(rs.next()){
				DbTable table = new DbTable();
				table.setDbName(rs.getString("TABLE_SCHEMA"));
				table.setTableName(rs.getString("TABLE_NAME"));
				table.setComment(rs.getString("TABLE_COMMENT"));
				java.sql.Date createTime = rs.getDate("CREATE_TIME");
				if(createTime != null){
					table.setCreateDatetime(new Date(createTime.getTime()));
				}
				result.add(table);
			}
			return result;
		}finally{
			if(rs != null){
				rs.close();
			}
			if(conn != null){
				conn.close();
			}
		}
	}

	public boolean existTable(String database,String tableName) throws SQLException {
		Connection conn = null;
		ResultSet rs = null;
		try{
			conn = getManageConn();
			PreparedStatement pstmt = conn.prepareStatement("select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_NAME = ?");
			pstmt.setString(1, tableName.toUpperCase()); //必须大写
			rs = pstmt.executeQuery();
			if(rs.next()){
				return rs.getInt(1) > 0;
			}
		}finally{
			if(rs != null){
				rs.close();
			}
			if(conn != null){
				conn.close();
			}
		}
		return false;
	}

	public List<DbColumn> findAllDbColumn(String database, String tableName)
			throws SQLException {
		Connection conn = null;
		ResultSet rs = null;
		try{
			List<DbColumn> result = new ArrayList<DbColumn>();
			conn = getManageConn();
			PreparedStatement pstmt = conn.prepareStatement("select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = ?");
			pstmt.setString(1, database.toUpperCase());
			pstmt.setString(2, tableName.toUpperCase());
			rs = pstmt.executeQuery();
			while(rs.next()){
				DbColumn column = new DbColumn();
				column.setDbName(rs.getString("TABLE_SCHEMA"));
				column.setTableName(rs.getString("TABLE_NAME"));
				column.setName(rs.getString("COLUMN_NAME"));
				column.setType(rs.getString("DATA_TYPE"));
				column.setCharMaxLength(rs.getInt("CHARACTER_MAXIMUM_LENGTH"));
				column.setColumnKey(rs.getString("COLUMN_KEY"));
				column.setDefaultValue(rs.getObject("COLUMN_DEFAULT"));
				column.setComment(rs.getString("COLUMN_COMMENT"));
				column.setIsNullable(rs.getBoolean("IS_NULLABLE"));
				result.add(column);
			}
			return result;
		}finally{
			if(rs != null){
				rs.close();
			}
			if(conn != null){
				conn.close();
			}
		}
	}

	public boolean existDb(String database) throws SQLException {
		return false;
	}

	@Override
	public boolean existView(String database, String viewName) throws SQLException {
		return false;
	}

	@Override
	public List<IndexInfo> findAllIndex(String tableName) throws SQLException {
		return null;
	}

	@Override
	public List<String> findAllUniqueKeyName(String database, String tableName) throws SQLException {
		return null;
	}

}
